"""
剪映模板仓库

提供剪映模板相关数据的专门访问方法。
"""

from typing import List, Optional, Dict, Any
from datetime import datetime

from sqlalchemy.orm import joinedload
from sqlalchemy import and_, or_, desc, asc, func

from src.core.di import Injectable
from src.database.repository import BaseRepository
from src.models.jianying_template import JianyingTemplate, JianyingVideoSegment


@Injectable(scope="singleton")
class JianyingTemplateRepository(BaseRepository[JianyingTemplate]):
    """
    剪映模板仓库
    
    提供剪映模板数据的专门访问方法和业务查询。
    """
    
    def __init__(self):
        super().__init__(JianyingTemplate)
    
    def get_by_template_id(self, template_id: str) -> Optional[JianyingTemplate]:
        """根据模板ID获取模板"""
        return self.get_one_by_field("template_id", template_id)
    
    def get_with_segments(self, template_id: str) -> Optional[JianyingTemplate]:
        """获取包含视频片段的模板"""
        session = self.get_session()
        try:
            return session.query(JianyingTemplate).options(
                joinedload(JianyingTemplate.video_segments)
            ).filter(JianyingTemplate.template_id == template_id).first()
        finally:
            session.close()
    
    def search_templates(self, 
                        duration_min: Optional[float] = None,
                        duration_max: Optional[float] = None,
                        canvas_ratio: Optional[str] = None,
                        min_segments: Optional[int] = None,
                        max_segments: Optional[int] = None,
                        limit: Optional[int] = None) -> List[JianyingTemplate]:
        """搜索模板"""
        session = self.get_session()
        try:
            query = session.query(JianyingTemplate)
            
            # 添加过滤条件
            if duration_min is not None:
                query = query.filter(JianyingTemplate.duration_seconds >= duration_min)
            
            if duration_max is not None:
                query = query.filter(JianyingTemplate.duration_seconds <= duration_max)
            
            if canvas_ratio:
                query = query.filter(JianyingTemplate.canvas_ratio == canvas_ratio)
            
            if min_segments is not None:
                query = query.filter(JianyingTemplate.video_segments_count >= min_segments)
            
            if max_segments is not None:
                query = query.filter(JianyingTemplate.video_segments_count <= max_segments)
            
            # 排序
            query = query.order_by(JianyingTemplate.template_name)
            
            # 限制结果数量
            if limit:
                query = query.limit(limit)
            
            return query.all()
        finally:
            session.close()
    
    def get_canvas_ratio_distribution(self) -> Dict[str, int]:
        """获取画布比例分布"""
        session = self.get_session()
        try:
            results = session.query(
                JianyingTemplate.canvas_ratio,
                func.count(JianyingTemplate.id).label('count')
            ).group_by(JianyingTemplate.canvas_ratio).all()
            
            return {ratio: count for ratio, count in results if ratio}
        finally:
            session.close()
    
    def get_duration_distribution(self) -> Dict[str, int]:
        """获取时长分布"""
        session = self.get_session()
        try:
            # 使用CASE语句进行分组
            from sqlalchemy import case
            results = session.query(
                case(
                    (JianyingTemplate.duration_seconds < 5, '0-5s'),
                    (JianyingTemplate.duration_seconds < 10, '5-10s'),
                    (JianyingTemplate.duration_seconds < 20, '10-20s'),
                    (JianyingTemplate.duration_seconds < 30, '20-30s'),
                    else_='30s+'
                ).label('duration_range'),
                func.count(JianyingTemplate.id).label('count')
            ).group_by('duration_range').all()

            return {duration_range: count for duration_range, count in results}
        finally:
            session.close()
    
    def get_statistics(self) -> Dict[str, Any]:
        """获取统计信息"""
        session = self.get_session()
        try:
            # 总模板数
            total_templates = session.query(func.count(JianyingTemplate.id)).scalar()
            
            # 总视频片段数
            total_segments = session.query(func.count(JianyingVideoSegment.id)).scalar()
            
            # 最近扫描时间
            latest_scan = session.query(func.max(JianyingTemplate.created_at)).scalar()
            
            return {
                'total_templates': total_templates or 0,
                'total_video_segments': total_segments or 0,
                'latest_scan': latest_scan.isoformat() if latest_scan else None,
                'canvas_ratio_distribution': self.get_canvas_ratio_distribution(),
                'duration_distribution': self.get_duration_distribution()
            }
        finally:
            session.close()
    
    def create_or_update_template(self, template_data: Dict[str, Any], 
                                 segments_data: List[Dict[str, Any]]) -> JianyingTemplate:
        """创建或更新模板及其视频片段"""
        session = self.get_session()
        try:
            template_id = template_data['template_id']
            
            # 查找现有模板
            existing_template = session.query(JianyingTemplate).filter(
                JianyingTemplate.template_id == template_id
            ).first()
            
            if existing_template:
                # 更新现有模板
                for key, value in template_data.items():
                    if hasattr(existing_template, key):
                        setattr(existing_template, key, value)
                
                # 删除旧的视频片段
                session.query(JianyingVideoSegment).filter(
                    JianyingVideoSegment.template_id == template_id
                ).delete()
                
                template = existing_template
            else:
                # 创建新模板
                template = JianyingTemplate(**template_data)
                session.add(template)
            
            # 添加视频片段
            for segment_data in segments_data:
                segment_data['template_id'] = template_id
                segment = JianyingVideoSegment(**segment_data)
                session.add(segment)
            
            session.commit()
            session.refresh(template)
            return template
            
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def delete_by_template_id(self, template_id: str) -> bool:
        """根据模板ID删除模板"""
        session = self.get_session()
        try:
            # 删除视频片段
            segments_deleted = session.query(JianyingVideoSegment).filter(
                JianyingVideoSegment.template_id == template_id
            ).delete()
            
            # 删除模板
            template_deleted = session.query(JianyingTemplate).filter(
                JianyingTemplate.template_id == template_id
            ).delete()
            
            session.commit()
            return template_deleted > 0
            
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def get_templates_by_canvas_ratio(self, canvas_ratio: str) -> List[JianyingTemplate]:
        """根据画布比例获取模板"""
        return self.get_all_by_field("canvas_ratio", canvas_ratio)
    
    def get_templates_by_duration_range(self, min_duration: float, max_duration: float) -> List[JianyingTemplate]:
        """根据时长范围获取模板"""
        session = self.get_session()
        try:
            return session.query(JianyingTemplate).filter(
                and_(
                    JianyingTemplate.duration_seconds >= min_duration,
                    JianyingTemplate.duration_seconds <= max_duration
                )
            ).order_by(JianyingTemplate.duration_seconds).all()
        finally:
            session.close()
    
    def get_recent_templates(self, limit: int = 10) -> List[JianyingTemplate]:
        """获取最近的模板"""
        session = self.get_session()
        try:
            return session.query(JianyingTemplate).order_by(
                desc(JianyingTemplate.created_at)
            ).limit(limit).all()
        finally:
            session.close()


@Injectable(scope="singleton")
class JianyingVideoSegmentRepository(BaseRepository[JianyingVideoSegment]):
    """
    剪映视频片段仓库
    
    提供视频片段数据的专门访问方法。
    """
    
    def __init__(self):
        super().__init__(JianyingVideoSegment)
    
    def get_by_template_id(self, template_id: str) -> List[JianyingVideoSegment]:
        """根据模板ID获取所有视频片段"""
        return self.get_all_by_field("template_id", template_id)
    
    def get_by_material_id(self, material_id: str) -> List[JianyingVideoSegment]:
        """根据素材ID获取视频片段"""
        session = self.get_session()
        try:
            return session.query(JianyingVideoSegment).filter(
                or_(
                    JianyingVideoSegment.material_id == material_id,
                    JianyingVideoSegment.local_material_id == material_id
                )
            ).all()
        finally:
            session.close()
    
    def get_segments_with_effects(self) -> List[JianyingVideoSegment]:
        """获取包含特效的视频片段"""
        session = self.get_session()
        try:
            return session.query(JianyingVideoSegment).filter(
                JianyingVideoSegment.effects_count > 0
            ).all()
        finally:
            session.close()
    
    def get_segments_by_duration_range(self, min_duration: float, max_duration: float) -> List[JianyingVideoSegment]:
        """根据时长范围获取视频片段"""
        session = self.get_session()
        try:
            return session.query(JianyingVideoSegment).filter(
                and_(
                    JianyingVideoSegment.target_duration >= min_duration,
                    JianyingVideoSegment.target_duration <= max_duration
                )
            ).order_by(JianyingVideoSegment.target_duration).all()
        finally:
            session.close()
    
    def delete_by_template_id(self, template_id: str) -> int:
        """根据模板ID删除所有视频片段"""
        session = self.get_session()
        try:
            deleted_count = session.query(JianyingVideoSegment).filter(
                JianyingVideoSegment.template_id == template_id
            ).delete()
            session.commit()
            return deleted_count
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
